const Database = require('better-sqlite3');
const path = require('path');
const fs = require('fs');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');
const BACKUP_PATH = path.join(process.cwd(), 'sql', 'backup', 'full-backup.sql');

console.log('🔄 从备份恢复实例表数据');
console.log('===========================');

try {
  // 1. 读取备份文件
  if (!fs.existsSync(BACKUP_PATH)) {
    console.log('❌ 备份文件不存在:', BACKUP_PATH);
    return;
  }
  
  const backupContent = fs.readFileSync(BACKUP_PATH, 'utf8');
  console.log('✅ 成功读取备份文件');
  
  // 2. 提取实例表的CREATE和INSERT语句
  const lines = backupContent.split('\\n');
  const instanceTableStatements = [];
  
  let inInstanceTable = false;
  let currentTableStatements = [];
  
  for (const line of lines) {
    if (line.includes('CREATE TABLE device_instances_')) {
      inInstanceTable = true;
      currentTableStatements = [line];
      console.log('📋 找到实例表:', line.match(/CREATE TABLE (\\w+)/)?.[1]);
    } else if (inInstanceTable) {
      currentTableStatements.push(line);
      
      // 如果遇到下一个CREATE TABLE或者结束，保存当前表的语句
      if (line.includes('CREATE TABLE') && !line.includes('device_instances_')) {
        // 移除最后一行（下一个表的CREATE语句）
        currentTableStatements.pop();
        instanceTableStatements.push(...currentTableStatements);
        inInstanceTable = false;
        currentTableStatements = [];
      }
    }
  }
  
  // 添加最后一个表的语句（如果有）
  if (currentTableStatements.length > 0) {
    instanceTableStatements.push(...currentTableStatements);
  }
  
  console.log(`📊 提取到 ${instanceTableStatements.length} 行实例表相关语句`);
  
  // 3. 连接数据库并执行恢复
  const db = new Database(DB_PATH);
  
  console.log('\\n🔄 开始恢复实例表...');
  
  let successCount = 0;
  let errorCount = 0;
  
  for (const statement of instanceTableStatements) {
    const trimmedStatement = statement.trim();
    if (!trimmedStatement || trimmedStatement === ');') continue;
    
    try {
      db.prepare(trimmedStatement).run();
      
      if (trimmedStatement.includes('CREATE TABLE device_instances_')) {
        successCount++;
        const tableName = trimmedStatement.match(/CREATE TABLE (\\S+)/)?.[1];
        console.log(`  ✅ 恢复表: ${tableName}`);
      }
    } catch (error) {
      // 忽略表已存在的错误
      if (!error.message.includes('already exists')) {
        console.log(`  ❌ 执行失败: ${trimmedStatement.substring(0, 50)}...`);
        console.log(`     错误: ${error.message}`);
        errorCount++;
      }
    }
  }
  
  db.close();
  
  // 4. 验证恢复结果
  console.log('\\n📊 恢复结果验证');
  console.log('===================');
  
  const dbCheck = new Database(DB_PATH);
  const restoredTables = dbCheck.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    ORDER BY name
  `).all();
  
  console.log(`✅ 成功恢复实例表: ${successCount} 个`);
  console.log(`❌ 恢复错误: ${errorCount} 个`);
  console.log(`📋 当前实例表总数: ${restoredTables.length} 个`);
  
  // 检查数据
  let totalRecords = 0;
  for (const table of restoredTables) {
    try {
      const count = dbCheck.prepare(`SELECT COUNT(*) as count FROM [${table.name}]`).get();
      totalRecords += count.count;
      if (count.count > 0) {
        console.log(`  📊 ${table.name}: ${count.count} 条记录`);
      }
    } catch (error) {
      console.log(`  ❌ 无法查询 ${table.name}: ${error.message}`);
    }
  }
  
  console.log(`\\n📊 实例数据总计: ${totalRecords} 条记录`);
  
  if (totalRecords > 0) {
    console.log('\\n🎉 成功恢复实例表和数据！');
    console.log('您的实例数据已经安全恢复。');
  } else {
    console.log('\\n⚠️  恢复的表中暂无数据，但表结构已恢复。');
  }
  
  dbCheck.close();
  
} catch (error) {
  console.error('❌ 恢复失败:', error.message);
}